DROP TABLE cyberplat_operators;

CREATE TABLE cyberplat_operators
(
  operator_id integer NOT NULL,
  operator_name character varying(255) NOT NULL,
  checkurl character varying(255) NOT NULL,
  payurl character varying(255) NOT NULL,
  statusurl character varying(255) NOT NULL,
  scheme_id integer NOT NULL,
  account character varying(64),
  fee numeric NOT NULL DEFAULT 0,
  CONSTRAINT cyberplat_operators_pk PRIMARY KEY (scheme_id, operator_id),
  CONSTRAINT schemeid FOREIGN KEY (scheme_id)
      REFERENCES schemes (scheme_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (
  OIDS=FALSE
);

DROP TABLE cyberplat_barcode;

CREATE TABLE cyberplat_barcode
(
  operator_id integer NOT NULL,
  internal_id character varying(20) NOT NULL,
  pattern character varying(100) NOT NULL,
  replacement character varying(100) NOT NULL DEFAULT E'\\1'::character varying,
  scheme_id integer,
  CONSTRAINT cyberplat_barcode_pk PRIMARY KEY (scheme_id, operator_id, internal_id, pattern),
  CONSTRAINT schemeid FOREIGN KEY (scheme_id)
      REFERENCES schemes (scheme_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (
  OIDS=FALSE
);

CREATE TYPE ware_by_barcode_t AS (
  internal_id character varying, price numeric, quantity numeric, min_price numeric, best_before timestamp without time zone, shortcut character varying, longtitle character varying, flags smallint, tax_group_id integer, auto_scheme_id integer, fix_scheme_id integer, barcode character varying, coefficient numeric, operator_id integer, account character varying, fee numeric
);

DROP FUNCTION ware_by_barcode(character varying);

CREATE OR REPLACE FUNCTION ware_by_barcode(IN p_barcode character varying)
  RETURNS SETOF ware_by_barcode_t
AS
$BODY$
BEGIN
RETURN QUERY
SELECT w.internal_id,
       w.price,
       w.quantity,
       w.min_price,
       w.best_before,
       w.shortcut,
       w.longtitle,
       w.flags,
       w.tax_group_id,
       w.auto_scheme_id,
       w.fix_scheme_id,
       wb.barcode,
       wb.coefficient,
       -1::integer,
       ''::character varying,
       0::numeric
FROM ware w,
     ware_barcode wb
WHERE wb.internal_id = w.internal_id
      AND wb.barcode = p_barcode;
IF NOT FOUND THEN
RETURN QUERY
SELECT w.internal_id,
       w.price,
       w.quantity,
       w.min_price,
       w.best_before,
       (w.shortcut||' '||regexp_replace(p_barcode, cb.pattern, cb.replacement))::character varying,
       (w.longtitle||' '||regexp_replace(p_barcode, cb.pattern, cb.replacement))::character varying,
       w.flags,
       w.tax_group_id,
       w.auto_scheme_id,
       w.fix_scheme_id,
       regexp_replace(p_barcode, cb.pattern, cb.replacement)::character varying,
       1::numeric(10,3),
       cb.operator_id,
       co.account,
       co.fee
FROM ware w
     join cyberplat_barcode cb on cb.internal_id=w.internal_id
     join cyberplat_operators co on co.operator_id=cb.operator_id
WHERE p_barcode ~* cb.pattern;
END IF;
RETURN;
END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100
  ROWS 1000;

DROP TABLE fix_reduction_options;

CREATE TABLE fix_reduction_options
(
  external_id integer NOT NULL,
  group_id integer,
  pay_type integer NOT NULL,
  "value" numeric(10,2) NOT NULL,
  hand_discount integer NOT NULL default 0,
  CONSTRAINT group_fk FOREIGN KEY (group_id)
      REFERENCES ware_group (group_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT pay_type_value CHECK (pay_type = ANY (ARRAY[0, 1])),
  CONSTRAINT hand_discount_value CHECK (hand_discount = ANY (ARRAY[0, 1]))
)
WITH (
  OIDS=FALSE
);
ALTER TABLE fix_reduction_options OWNER TO kassir;

CREATE INDEX fix_reduction_options_idx
  ON fix_reduction_options
  USING btree
  (group_id);

CREATE INDEX fix_reduction_options_idx1
  ON fix_reduction_options
  USING btree
  (external_id, group_id, pay_type);

CREATE TYPE reduction_val_t AS (
  val numeric, hand_discount integer
);

CREATE OR REPLACE FUNCTION get_fix_reduction_value(p_fix_reduction_external_id integer, p_ware_internal_id character varying, p_pay_type integer)
  RETURNS SETOF reduction_val_t AS
$BODY$
DECLARE
  v_group_id integer;

  v_val_def numeric;
  v_val numeric;
  v_hd integer;

  r reduction_val_t;
BEGIN
  select coalesce(value, 0)
         into v_val_def
  from fix_reduction
  where external_id=p_fix_reduction_external_id;

  select group_id
         into v_group_id
  from ware
  where internal_id=p_ware_internal_id;
  
  loop
    select wg.parent_group_id, fro.value, fro.hand_discount
           into v_group_id, v_val, v_hd
    from ware_group wg
         left join fix_reduction_options fro on fro.group_id=wg.group_id
                                            and fro.pay_type=p_pay_type
                                            and fro.external_id=p_fix_reduction_external_id
    where wg.group_id=v_group_id;

    exit when v_val is not null or v_group_id is null;
  end loop;

  if v_group_id is null and v_val is null then
    select value, hand_discount into v_val, v_hd
    from fix_reduction_options
    where external_id=p_fix_reduction_external_id
          and group_id is null
          and pay_type=p_pay_type;
  end if;

  r.val := coalesce(v_val, v_val_def);
  r.hand_discount := coalesce(v_hd, 0);
  return next r;
  return;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

INSERT INTO parameters (param_id, param_name, param_type, default_value) VALUES (400, 'CyberplatTimeout', 'int', '30');
INSERT INTO stringparameters (stringparam_id, stringparam_name, default_value) VALUES (1002, 'CyberplatBankKey', '');
INSERT INTO stringparameters (stringparam_id, stringparam_name, default_value) VALUES (1003, 'CyberplatProxy', NULL);
INSERT INTO stringparameters (stringparam_id, stringparam_name, default_value) VALUES (1004, 'CyberplatSSL', 'SSLv3:RC4-MD5');
INSERT INTO stringparameters (stringparam_id, stringparam_name, default_value) VALUES (1006, 'CyberplatPointPass', '');
INSERT INTO stringparameters (stringparam_id, stringparam_name, default_value) VALUES (1005, 'CyberplatPointKeys', '');
INSERT INTO stringparameters (stringparam_id, stringparam_name, default_value) VALUES (1007, 'CyberplatPointSD', '');
INSERT INTO stringparameters (stringparam_id, stringparam_name, default_value) VALUES (1008, 'CyberplatPointAP', '');
INSERT INTO stringparameters (stringparam_id, stringparam_name, default_value) VALUES (1009, 'CyberplatPointOP', '');
INSERT INTO stringparameters (stringparam_id, stringparam_name, default_value) VALUES (1010, 'CyberplatCheckHeader', '');
INSERT INTO stringparameters (stringparam_id, stringparam_name, default_value) VALUES (1011, 'CyberplatCheckFooter', '');
